﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using HK2009.Common;
using HK2009.BusinessFacade;
using HK2009.BusinessFacadeHandler;
using HK2009.EntityQuery;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
public partial class SystemManage_ClientBatchImport : BasePage
{
    ISyClientHandler clientManage = new SyClientHandler();
    ISyPlaceHandler placeManage = new SyPlaceHandler();
    IRolePurviewHandler userManage = new RolePurviewHandler();

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    private DataTable NameExists(string nameCN, string nameHK, string nameEN)
    {

        SqlParameter[] prams = new SqlParameter[3];
        prams[0] = new SqlParameter("@CNShortName", nameCN);
        prams[1] = new SqlParameter("@HKShortName", nameHK);
        prams[2] = new SqlParameter("@ENShortName", nameEN);

        string sql = "select * from Sy_Client where CNShortName = @CNShortName and HKShortName = @HKShortName and  ENGShortName = @ENShortName";
        DataTable dt = clientManage.GetClientList(sql, prams);

        return dt;
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (upFile.FileName != string.Empty)
        {
            if (Path.GetExtension(upFile.FileName) == ".xls" || Path.GetExtension(upFile.FileName) == ".xlsx")
            {

                string fileUrl = FileTools.MakeDirByMonth("/ImportTemplete") + FileTools.AutoFileName() + Path.GetExtension(upFile.FileName);
                //保存文件
                upFile.SaveAs(Server.MapPath("~").ToString() + fileUrl);


                DataTable dt = BackupToExcel.IpmortExcel(System.Web.HttpContext.Current.Server.MapPath(fileUrl));
                string strFailed = string.Empty;

                try
                {
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (dt.Rows[i]["CNShortName"].ToString() != string.Empty || dt.Rows[i]["HKShortName"].ToString() != string.Empty || dt.Rows[i]["ENShortName"].ToString() != string.Empty)
                            {
                                DataTable dtModel = NameExists(dt.Rows[i]["CNShortName"].ToString(), dt.Rows[i]["HKShortName"].ToString(), dt.Rows[i]["ENShortName"].ToString());
                                if (dtModel != null && dtModel.Rows.Count > 0)
                                {
                                    if (strFailed != "")
                                    {
                                        strFailed += ",";
                                    }
                                    strFailed += dt.Rows[i]["ENShortName"].ToString();
                                }
                                else
                                {

                                    SqlParameter[] parmsI = null;
                                    List<SqlParameter> lst = new List<SqlParameter>();
                                    string sqlStr = string.Empty;

                                    lst.Add(new SqlParameter("@CNShortName", dt.Rows[i]["CNShortName"].ToString()));
                                    lst.Add(new SqlParameter("@HKShortName", dt.Rows[i]["HKShortName"].ToString()));
                                    lst.Add(new SqlParameter("@ENGShortName", dt.Rows[i]["ENShortName"].ToString()));

                                    lst.Add(new SqlParameter("@CNName", dt.Rows[i]["CNName"].ToString()));
                                    lst.Add(new SqlParameter("@HKName", dt.Rows[i]["HKName"].ToString()));
                                    lst.Add(new SqlParameter("@ENGName", dt.Rows[i]["ENName"].ToString()));

                                    lst.Add(new SqlParameter("@CNAddress", dt.Rows[i]["CNAddress"].ToString()));
                                    lst.Add(new SqlParameter("@HKAddress", dt.Rows[i]["HKAddress"].ToString()));
                                    lst.Add(new SqlParameter("@ENGAddress", dt.Rows[i]["ENAddress"].ToString()));

                                    lst.Add(new SqlParameter("@Email", dt.Rows[i]["Email"].ToString()));
                                    lst.Add(new SqlParameter("@Website", dt.Rows[i]["Website"].ToString()));
                                    lst.Add(new SqlParameter("@PShiper", dt.Rows[i]["PShiper"].ToString()));

                                    lst.Add(new SqlParameter("@LocalBP", dt.Rows[i]["LocalBP"].ToString()));


                                    string strplace = dt.Rows[i]["Place"].ToString();

                                    int id = placeManage.GetPlaceIDByName(strplace.Trim());//根据名称获取placeID

                                    lst.Add(new SqlParameter("@PlaceID", id.ToString()));


                                    lst.Add(new SqlParameter("@MVendorCode", dt.Rows[i]["MVendorCode"].ToString()));
                                    lst.Add(new SqlParameter("@HVendorCode", dt.Rows[i]["HVendorCode"].ToString()));
                                                                        

                                    lst.Add(new SqlParameter("@ToYantian", dt.Rows[i]["ToYantian"] != null && dt.Rows[i]["ToYantian"].ToString()!=string.Empty?int.Parse(dt.Rows[i]["ToYantian"].ToString()):0));
                                    lst.Add(new SqlParameter("@ToShekou", dt.Rows[i]["ToShekou"] != null && dt.Rows[i]["ToShekou"].ToString() != string.Empty ? int.Parse(dt.Rows[i]["ToShekou"].ToString()) : 0));
                                    lst.Add(new SqlParameter("@GPSWebsite", dt.Rows[i]["GPSWebsite"].ToString()));



                                    string strCompUserName = dt.Rows[i]["CompanyBP"].ToString();
                                    int compUserID = userManage.GetUserIDByLoginID(strCompUserName);
                                    lst.Add(new SqlParameter("@CompanyBP",compUserID));


                                    lst.Add(new SqlParameter("@Remark", dt.Rows[i]["Remark"].ToString()));

                                    int status = 1;
                                    int.TryParse(dt.Rows[i]["Status"].ToString(), out status);
                                    lst.Add(new SqlParameter("@Status", status.ToString()));


                                    SqlParameter parm = new SqlParameter("@ReturnValue", 0);
                                    parm.SqlDbType = SqlDbType.Int;
                                    parm.Direction = ParameterDirection.Output;
                                    lst.Add(parm);
                                    lst.Add(new SqlParameter("@Code", string.Empty));

                                    lst.Add(new SqlParameter("@Title", string.Empty));
                                    lst.Add(new SqlParameter("@Place", string.Empty));
                                    lst.Add(new SqlParameter("@BRLinkMan", string.Empty));
                                    lst.Add(new SqlParameter("@BRTelNo", string.Empty));
                                    lst.Add(new SqlParameter("@BRFax", string.Empty));
                                    lst.Add(new SqlParameter("@BRMPhone", string.Empty));

                                    lst.Add(new SqlParameter("@FRLinkMan", string.Empty));
                                    lst.Add(new SqlParameter("@FRTelNo", string.Empty));
                                    lst.Add(new SqlParameter("@FRFax", string.Empty));
                                    lst.Add(new SqlParameter("@FRBank", string.Empty));
                                    lst.Add(new SqlParameter("@FRAccount", string.Empty));

                                    lst.Add(new SqlParameter("@HKBROffice", string.Empty));
                                    lst.Add(new SqlParameter("@HKBRTelNo", string.Empty));
                                    lst.Add(new SqlParameter("@HKBREmail", string.Empty));
                                    lst.Add(new SqlParameter("@HKBRLinkMan", string.Empty));
                                    lst.Add(new SqlParameter("@HKBRFax", string.Empty));
                                    lst.Add(new SqlParameter("@HKBRCustoms", string.Empty));

                                    lst.Add(new SqlParameter("@LRHKLinkMan", string.Empty));
                                    lst.Add(new SqlParameter("@LRHKTelNo", string.Empty));
                                    lst.Add(new SqlParameter("@LRHKFax", string.Empty));
                                    lst.Add(new SqlParameter("@LRHKEmail", string.Empty));

                                    lst.Add(new SqlParameter("@LRSZLinkMan", string.Empty));
                                    lst.Add(new SqlParameter("@LRSZTelNo", string.Empty));
                                    lst.Add(new SqlParameter("@LRSZFax", string.Empty));
                                    lst.Add(new SqlParameter("@LRSZEmail", string.Empty));
                                    lst.Add(new SqlParameter("@ENGTitle", string.Empty));
                                    lst.Add(new SqlParameter("@HKTitle", string.Empty));

                                    if (Session["UserID"] != null)
                                    {
                                        lst.Add(new SqlParameter("@LastModUser", Session["UserID"].ToString()));
                                    }
                                    else
                                    {
                                        lst.Add(new SqlParameter("@LastModUser", "0"));

                                    }
                                    lst.Add(new SqlParameter("@LastModDate", DateTime.Now.ToString("yyyy-MM-dd")));


                                    sqlStr = @"INSERT INTO Sy_Client(Code,CNShortName,ENGShortName,CNName,ENGName,CNAddress,ENGAddress,Title,Email,WebSite,
                          PShiper,LocalBP,Place,BRLinkMan,BRTelNo,BRFax,BRMPhone,FRLinkMan,FRTelNo,FRFax,FRBank,FRAccount,HKBROffice,
                          HKBRTelNo,HKBREmail,HKBRLinkMan,HKBRFax,HKBRCustoms,LRHKLinkMan,LRHKTelNo,LRHKFax,LRHKEmail,LRSZLinkMan,
                          LRSZTelNo,LRSZFax,LRSZEmail,Remark,Status,LastModUser,LastModDate,HKShortName,HKName,HKAddress,ENGTitle,HKTitle,PlaceID,CompanyBP,MVendorCode,HVendorCode,ToYantian,ToShekou,GPSWebsite) VALUES(@Code,@CNShortName,@ENGShortName,@CNName,@ENGName,@CNAddress,@ENGAddress,@Title,@Email,@WebSite,
                          @PShiper,@LocalBP,@Place,@BRLinkMan,@BRTelNo,@BRFax,@BRMPhone,@FRLinkMan,@FRTelNo,@FRFax,@FRBank,@FRAccount,@HKBROffice,
                          @HKBRTelNo,@HKBREmail,@HKBRLinkMan,@HKBRFax,@HKBRCustoms,@LRHKLinkMan,@LRHKTelNo,@LRHKFax,@LRHKEmail,@LRSZLinkMan,
                          @LRSZTelNo,@LRSZFax,@LRSZEmail,@Remark,@Status,@LastModUser,@LastModDate,@HKShortName,@HKName,@HKAddress,@ENGTitle,@HKTitle,@PlaceID,@CompanyBP,@MVendorCode,@HVendorCode,@ToYantian,@ToShekou,@GPSWebsite) SET @ReturnValue=@@Identity;";

                                    if (dt.Rows[i]["Type"].ToString() != string.Empty)  //获取分类名，
                                    {
                                        string strtype = dt.Rows[i]["Type"].ToString();
                                        if (strtype.EndsWith(","))
                                        {
                                            strtype = strtype.Substring(0, strtype.Length - 1);
                                        }

                                        string[] types = strtype.Split(new char[] { ',' });
                                        for (int j = 0; j < types.Length; j++)
                                        {
                                            int typeID = clientManage.GetTypeIDByTypeName(types[j].Trim());//根据名称获取typeID
                                            if (typeID != 0)
                                            {
                                                lst.Add(new SqlParameter("@TypeID" + j.ToString(), typeID.ToString()));

                                                sqlStr += "INSERT INTO Sy_ClientType(ClientID,SyCodeID) VALUES(@ReturnValue,@TypeID" + j.ToString() + ");";
                                            }
                                        }
                                    }

                                    if (dt.Rows[i]["LinkMan"].ToString() != string.Empty)
                                    {
                                        string linkMans = dt.Rows[i]["LinkMan"].ToString();
                                        string[] _linkMan = linkMans.Split(new char[] { ';' });

                                        for (int l = 0; l < _linkMan.Length; l++)
                                        {
                                            string[] _PerLinkMan = _linkMan[l].Split(new char[] { ',' });

                                            lst.Add(new SqlParameter("@LinkMan" + l.ToString(), _PerLinkMan[0]));
                                            lst.Add(new SqlParameter("@LTelNo" + l.ToString(), _PerLinkMan[1]));
                                            lst.Add(new SqlParameter("@LMPhone" + l.ToString(), _PerLinkMan[2]));
                                            lst.Add(new SqlParameter("@LFax" + l.ToString(), _PerLinkMan[3]));
                                            lst.Add(new SqlParameter("@LEmail" + l.ToString(), _PerLinkMan[4]));
                                            lst.Add(new SqlParameter("@LPosition" + l.ToString(), _PerLinkMan[5]));
                                            lst.Add(new SqlParameter("@LAddress" + l.ToString(), _PerLinkMan[6]));
                                            lst.Add(new SqlParameter("@LRemark" + l.ToString(), _PerLinkMan[7]));

                                            sqlStr += @"INSERT INTO Sy_ClientLinkMan(ClientID,LinkMan,TelNo,MPhone,Fax,Email,Position,Address,Remark)
                               VALUES(@ReturnValue,@LinkMan" + l.ToString() + ",@LTelNo" + l.ToString() + ",@LMPhone" + l.ToString() + ",@LFax" + l.ToString() + ",";

                                            sqlStr += "@LEmail" + l.ToString() + ",@LPosition" + l.ToString() + ",@LAddress" + l.ToString() + ",@LRemark" + l.ToString() + ")";
                                        }
                                    }

                                    parmsI = lst.ToArray();
                                    clientManage.ClientEdit(sqlStr, parmsI);
                                }
                            }
                        }

                        if (strFailed != "")
                        {
                            Jscript.Alert("导入成功，其中有部分数据因为已经存在，无法导入.这些数据是：" + strFailed, this.Page);
                        }
                        else
                        {
                            Jscript.Alert("导入成功", this.Page);
                        }
                    }

                    else
                    {
                        Jscript.Alert("无数据信息导入，请检查导入的文件是否有数据或者文件格式是否正确！", this.Page);
                    }
                }
                catch
                {
                    Jscript.Alert("导入过程中发生了异常，请与管理员联系！");

                }
            }
            else
            {
                Jscript.Alert("导入格式错误,导入的文件必须是.xls格式!", this.Page);
                return;
            }
        }
        else
        {
            Jscript.Alert("请先上传要导入的文件!", this.Page);
            return;
        }
    }
    protected void btnReset_Click(object sender, EventArgs e)
    {
        Response.Redirect("ClientList.aspx");
    }
}
